# 管道文件
# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import openpyxl
import pymysql
import json
class DbSuZhouList:
    def __init__(self):
      # 开启连接
        self.conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd='cl123456', db='douban', charset='utf8mb4')  
        # 游标
        self.cursor = self.conn.cursor()
        pass
    def close_spider(self, spider):
        self.conn.commit()
        self.conn.close()
        pass
    def process_item(self, item, spider):
        title = item.get('title','')
        imgs = item.get('imgs','')
        imgs_json = json.dumps(imgs)
        money = item.get('money','')
        server = item.get('server','')
        wx = item.get('wx','')
        qq = item.get('qq','')
        phone = item.get('phone','')
     
        # 插入数据 三个占位符douban.suzhoulist nanjinlist
        sql ='insert into douban.nanjinlist (title,imgs,money,server,wx,qq,phone) values(%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE qq=qq;'
        self.cursor.execute(
            sql,
            (title,imgs_json,money,server,wx,qq,phone)
        )
 
        return item

# 写入 数据库
class DbPipeline:
    def __init__(self):
        # 开启连接
        self.conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd='cl123456', db='douban', charset='utf8mb4')  
        # 游标
        self.cursor = self.conn.cursor()
        pass
    def close_spider(self, spider):
        self.conn.commit()
        self.conn.close()
        pass
    def process_item(self, item, spider):
        title = item.get('title','')
        rank = item.get('rank','')
        subject = item.get('subject','')
        
        # 插入数据 三个占位符
        self.cursor.execute(
            'insert into douban.doubanlist (title,rating) values(%s,%s) ON DUPLICATE KEY UPDATE title=title;',
            (title,rank)
        )
        return item

class Spider2107Pipeline:
    # 管道初始化 钩子函数
    def __init__(self):
        # 新建工作簿 openpyxl库
        self.wb = openpyxl.Workbook()
        self.ws = self.wb.active
        self.ws.title = 'Top250'
        self.ws.append(('标题','评分','主题'))
        pass
    # 关闭时 钩子
    def close_spider(self, spider):
        self.wb.save('电影数据.xlsx')
        pass
    # 处理数据
    def process_item(self, item, spider):
        title = item.get('title','')
        rank = item.get('rank','')
        subject = item.get('subject','')
        self.ws.append((title,rank,subject))
        return item
